package org.programmers.anonymous.fightrobots.utils;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class JDBCSchema {

	final Logger logger = LoggerFactory.getLogger(JDBCSchema.class); // si hace falta logger.warn("...")
	
	protected Connection con = null;
	protected Statement ps = null;
	/**
	 * 
	 * @param fileName
	 * @return
	 */
	protected String readQueryFromFile (String fileName)
	{
		StringBuffer strBuffer = null;
		try
		{
			FileInputStream fstream = new FileInputStream(fileName);
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			strBuffer = new StringBuffer();
			String strLine;

			while((strLine = br.readLine()) != null)
			{
				strBuffer.append(strLine);
			}
		}
		catch (Exception e) 
		{  
			logger.error(e.getLocalizedMessage());
		}
		return strBuffer.toString();
	}

	/**
	 * Execute directly ONE query sentence
	 * @param query
	 */
	public void executeSqlScript (String query)
	{
		openConection(true);
		
		try {
			ps = con.createStatement();
			ps.execute(query);

		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
		}
		finally
		{
			closeConection();	
		}
	}
	
	/**
	 * 
	 * @param query
	 * @throws SQLException 
	 */
	public void addBatchQuery(String query) throws SQLException
	{
	  openConection(false);
	  
	  if (ps == null ) ps = con.createStatement();
	  
	  ps.addBatch(query);
	}
	
	public void executeBatchQuery() throws SQLException
	{
		try 
		{
			ps.executeBatch();
			con.commit();
		} 
		catch (SQLException e) 
		{
			con.rollback();
			logger.error(e.getLocalizedMessage());
		}
		finally
		{
			closeConection();
		}
		
	}

	private Connection openConection()
	{
		return openConection(true);
	}
	
	//Metodo para abrir la conexion
	private Connection openConection(boolean isAutocommitEnabled)
	{
		try 
		{
			if (con != null && con.getAutoCommit() != isAutocommitEnabled) 
			{
				con.commit(); // if any sql sentence is in the cache
				closeConection();
				openConection(isAutocommitEnabled);
			}			
/*			else if (con != null && con.getAutoCommit() == isAutocommitEnabled)
			{
				return con;
			}
			*/
			else if (con == null)   
			{
				Class.forName("com.mysql.jdbc.Driver");
				con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
				con.setAutoCommit(isAutocommitEnabled);
				logger.info(" ... Connected");
			}
		} 
		catch (ClassNotFoundException e) 
		{
			logger.error("No se ha encontrado el Driver de la BD" +  e.getLocalizedMessage());
		} 
		catch (SQLException e) 
		{
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				logger.warn(e1.getLocalizedMessage());
			}
			logger.error("No se ha podido conectar a la base de datos" + e.getLocalizedMessage());
		}    

		return con;
	}

	//Metodo para cerrar la conexion
	private void closeConection()
	{
		try 
		{
			logger.info(" ... closing connection");
			con.close();
			con = null;
			
			if (ps != null) 
			{
				ps.close();
				ps = null;
			}
			
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
		}
	}

	public void executeSQLFile (String fileName)
	{
		String query = readQueryFromFile(fileName);
		
		logger.debug(query);
		
		String[] listOfQuerys = query.split(";");
		
		try
		{
			for ( int a = 0; a< listOfQuerys.length;a++){
				
				addBatchQuery(listOfQuerys[a]+";");
				logger.debug(listOfQuerys[a]);
			}
			
			executeBatchQuery();
	
		}
		catch(SQLException e)
		{
			logger.error(e.getLocalizedMessage());
		}
	}


}